package com.example.mapper;

import com.example.pojo.Inheritor;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface InheritorMapper {

    //获取所有传承人
    @Select("SELECT i.id, i.name, i.personal_introduction personalIntroduction, i.sex, i.birthday, i.card, i.nation, i.region_id regionId, i.species_id speciesId, i.experience, i.representative_works representativeWorks, i.story, i.url,i.project_id projectId,p.region_name regionName,s.species_name speciesName,h.name projectName " +
            "FROM inheritor i " +
            "LEFT JOIN heritage_category_region p ON i.region_id = p.id " +
            "LEFT JOIN heritage_category_species s ON i.species_id = s.id " +
            "LEFT JOIN heritage_project h ON i.project_id = h.id ")// 使用 JOIN 查询
    List<Inheritor> findAll();

    // 根据传承人ID查询传承人
    @Select("SELECT i.id, i.name, i.personal_introduction, i.sex, i.birthday, i.card, i.nation, i.species_id, i.region_id, i.experience, i.representative_works, i.story, i.url, i.project_id projectId,p.region_name regionName,s.species_name speciesName,h.name projectName " +
            "FROM inheritor i " +
            "LEFT JOIN heritage_category_region p ON i.region_id = p.id " +
            "LEFT JOIN heritage_category_species s ON i.species_id = s.id " +
            "LEFT JOIN heritage_project h ON i.project_id = h.id " +
            "WHERE i.id = #{id}")
    Inheritor findById(@Param("id") Integer id);

    // 根据分类查询传承人
    @Select("SELECT h.id, h.name, h.personal_introduction personalIntroduction, h.sex, h.birthday, h.card, h.nation, h.region_id regionId, h.species_id speciesId, h.experience, h.representative_works representativeWorks, h.story, h.url,h.project_id projectId,r.region_name regionName,s.species_name speciesName " +
            "FROM inheritor h " +
            "LEFT JOIN heritage_category_region r ON h.region_id = r.id " +
            "LEFT JOIN heritage_category_species s ON h.species_id = s.id " +
            "WHERE (#{regionId} IS NULL OR h.region_id = #{regionId}) " +
            "AND (#{speciesId} IS NULL OR h.species_id = #{speciesId})")
    List<Inheritor> findAllByCategory(@Param("regionId") Integer regionId, @Param("speciesId") Integer speciesId);

    //根据非遗项目查询传承人
    @Select("SELECT i.id, i.name, i.personal_introduction as personalIntroduction, i.sex, i.birthday, i.card, i.nation,i.region_id regionId,i.species_id speciesId ," +
            "s.species_name as speciesName, r.region_name as regionName, " +
            "i.experience, i.representative_works as representativeWorks, i.story, i.url,i.project_id projectId,h.name as projectName " +
            "FROM inheritor i " +
            "LEFT JOIN heritage_project h ON i.project_id= h.id " +
            "LEFT JOIN heritage_category_species s ON i.species_id = s.id " +
            "LEFT JOIN heritage_category_region r ON i.region_id = r.id " +
            "WHERE h.id = #{projectId}")
    List<Inheritor> findPersonsByProjectId(@Param("projectId") Integer projectId);

    // 增加传承人
    @Insert("INSERT INTO inheritor (name, personal_introduction, sex, birthday, card, nation, region_id, species_id, experience, representative_works, story, url,project_id) " +
            "VALUES (#{name}, #{personalIntroduction}, #{sex}, #{birthday}, #{card}, #{nation}, #{regionId}, #{speciesId}, #{experience}, #{representativeWorks}, #{story}, #{url},#{projectId})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insertInheritor(Inheritor inheritor);

    // 根据地区名称查询地区ID
    @Select("SELECT id FROM heritage_category_region WHERE region_name = #{regionName}")
    Integer findRegionIdByName(@Param("regionName") String regionName);
    // 根据种类名称查询种类ID
    @Select("SELECT id FROM heritage_category_species WHERE species_name = #{speciesName}")
    Integer findSpeciesIdByName(@Param("speciesName") String speciesName);


    // 删除传承人
    @Delete("DELETE FROM inheritor WHERE id = #{id}")
    void deleteInheritorById(@Param("id") Integer id);

    // 更新传承人信息
    @Update("UPDATE inheritor SET name = #{name}, personal_introduction = #{personalIntroduction}, sex = #{sex}, birthday = #{birthday}, card = #{card}, nation = #{nation}, region_id = #{regionId}, species_id = #{speciesId}, experience = #{experience}, representative_works = #{representativeWorks}, story = #{story}, url = #{url},project_id = #{projectId} " +
            "WHERE id = #{id}")
    void updateInheritor(Inheritor inheritor);

    //插入传承人和非遗项目关联关系
    @Insert("INSERT INTO inherit (inheritor_id, heritage_project_id) VALUES (#{inheritorId}, #{heritageProjectId})")
    void insertInherit(@Param("inheritorId") Integer inheritorId, @Param("heritageProjectId") Integer heritageProjectId);

    //根据非遗项目名称查询项目ID
    @Select("SELECT id FROM heritage_project WHERE name = #{projectName}")
    Integer findProjectIdByProjectName(@Param("projectName") String projectName);
}
